using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DataAccessLayer;
using BusinessLayer;
using ObjectMap;

namespace DbfToAccess.Frm
{
    public partial class FrmMassExport : Form
    {
        public string PathAccess
        {
            get
            {
                return this.txtPathAccess.Text;
            }
        }

        public string MasaPajak
        {
            get
            {
                return ddMasaPajak.SelectedItem.ToString();
            }
        }

        public string TahunPajak
        {
            get
            {
                return this.cmbYearTax.SelectedItem.ToString();
            }
        }

        public string MainNPWP
        {
            get
            {
                return this.txtNPWP.Text;
            }
        }

        public FrmMassExport()
        {
            InitializeComponent();
            BindDDFormPajak();
            BindDDMasaPajak();
            BindcmbYearTax();
        }

        private void BindcmbYearTax()
        {
            ArrayList lstYear = new ArrayList();
            int thisYear = DateTime.Now.Year;

            for (int i = DateTime.Now.AddYears(-1).Year; i <= thisYear; i++)
            {
                lstYear.Add(i);
            }

            cmbYearTax.DataSource = lstYear;
        }

        private void BindDDMasaPajak()
        {
            ArrayList lstBulan = new ArrayList();
            lstBulan.Add("1");
            lstBulan.Add("2");
            lstBulan.Add("3");
            lstBulan.Add("4");
            lstBulan.Add("5");
            lstBulan.Add("6");
            lstBulan.Add("7");
            lstBulan.Add("8");
            lstBulan.Add("9");
            lstBulan.Add("10");
            lstBulan.Add("11");
            lstBulan.Add("12");

            ddMasaPajak.DataSource = lstBulan;
        }

        private void BindDDFormPajak()
        {
            ArrayList list = new ArrayList();
            list.Add("1721-A1");
            list.Add("REF_JABATAN_A1");

            ddFormPajak.DataSource = list;
        }

        private string NPWP(DataTable dt)
        {
            string npwp = string.Empty;

            foreach (DataRow dr in dt.Rows)
            {
                npwp = dr[0].ToString();
            }

            return npwp;
        }

        private void btnDestination_Click(object sender, EventArgs e)
        {
            this.fBrowsDialogMDB.RootFolder = System.Environment.SpecialFolder.MyComputer;
            this.fBrowsDialogMDB.ShowNewFolderButton = false;
            DialogResult result = this.fBrowsDialogMDB.ShowDialog();
            if (result == DialogResult.OK)
            {
                this.clearChkListMDB();

                // retrieve the name of the selected folder
                string foldername = this.fBrowsDialogMDB.SelectedPath;

                // print the folder name on a label
                this.txtPathAccess.Text = foldername;

                // iterate over all files in the selected folder and add them to 
                // the listbox.
                foreach (string filename in Directory.GetFiles(foldername))
                    this.chkListMDB.Items.Add(filename);
            }
        }

        private void clearChkListMDB()
        {
            this.chkListMDB.Items.Clear();
        }

        private void btnSource_Click(object sender, EventArgs e)
        {
            oFileDialogDBF.Title = "Open DBF File";
            oFileDialogDBF.Filter = "DBF Files|*.dbf";
            oFileDialogDBF.CheckFileExists = true;
            oFileDialogDBF.CheckPathExists = true;
            oFileDialogDBF.InitialDirectory = @"C:\";
            oFileDialogDBF.ShowDialog();
            txtPathDBF.Text = oFileDialogDBF.FileName.ToString();
        }

        private List<ObjectMap.Data1721A1> ListWithBranch(List<ObjectMap.Data1721A1> dt, string branchCode)
        {
            List<ObjectMap.Data1721A1> dtBranch = new List<ObjectMap.Data1721A1>();

            foreach (ObjectMap.Data1721A1 dr in dt)
            {
                if (dr.BranchCode.Trim() == branchCode.Trim())
                {
                    dtBranch.Add(dr);
                }
            }

            return dtBranch;
        }

        private List<ObjectMap.Data1721A1> Data1721A1ToCollection(DataTable dt, string pathAccess)
        {
            List<ObjectMap.Data1721A1> olist = new List<ObjectMap.Data1721A1>();
            string kodeForm = "D113248";
            int i = Tools.GetLastNumber("NO_URUT", "DAT_1721A1", pathAccess);
            string masaPajak1 = string.Empty;
            string masaPajak2 = string.Empty;

            ObjectMap.Data1721A1 oMap;

            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    string msA = dr[9].ToString();
                    string msZ = dr[10].ToString();

                    string npwpPegawai = string.Empty;
                    int length = 0;
                    length = dr[8].ToString().Length;

                    if (dr[8].ToString() == "0")
                    {
                        npwpPegawai = "000000000000000";
                    }
                    else if (dr[8].ToString() == string.Empty)
                    {
                        npwpPegawai = "000000000000000";
                    }
                    else
                    {
                        npwpPegawai = Tools.JoinWithoutDot(dr[8].ToString());
                    }

                    if (msA.Length == 1)
                    {
                        masaPajak1 = 0 + msA + TahunPajak;
                    }
                    else
                    {
                        masaPajak1 = msA + TahunPajak;
                    }

                    if (msZ.Length == 1)
                    {
                        masaPajak2 = 0 + msZ + TahunPajak;
                    }
                    else
                    {
                        masaPajak2 = msZ + TahunPajak;
                    }
                    oMap = new ObjectMap.Data1721A1();

                    oMap.BranchCode = dr[3].ToString();
                    oMap.KdForm = kodeForm;
                    //oMap.NoUrut = i;
                    oMap.NoUrut = Convert.ToInt32(dr[0]);
                    oMap.NpwpPerusahaan = MainNPWP;
                    oMap.TahunPajak = Convert.ToInt32(TahunPajak);
                    oMap.KdPembentulanKe = 0;
                    oMap.NpwpPegawai = npwpPegawai;
                    oMap.NamaPegawai = (dr[5] is DBNull) ? string.Empty : Tools.RemovePetik(dr[5].ToString());
                    oMap.AlamatPegawai = dr[16].ToString();
                    oMap.JabatanPegawai = dr[14].ToString();
                    oMap.JenisKelamin = Tools.JenisKelamin(dr[6].ToString());
                    oMap.FlagAsing = 0;
                    oMap.StatusKawin = Tools.KodeStatus(dr[7].ToString());
                    oMap.StatusKeluarga = Tools.KodeStatus(dr[7].ToString());
                    oMap.JumlahTanggungan = Tools.JmlTanggungan(dr[7].ToString());
                    oMap.Masa1 = masaPajak1.ToString();
                    oMap.Masa2 = masaPajak2.ToString();
                    oMap.Angka1 = (dr[50] is DBNull) ? 0 : Convert.ToDouble(dr[50]);
                    oMap.Angka2 = 0;
                    oMap.Angka3 = (dr[51] is DBNull) ? 0 : Convert.ToDouble(dr[51]);
                    oMap.Angka4 = 0;
                    oMap.Angka5 = (dr[52] is DBNull) ? 0 : Convert.ToDouble(dr[52]);
                    oMap.Angka6 = 0;
                    oMap.Angka7 = (dr[53] is DBNull) ? 0 : Convert.ToDouble(dr[53]);
                    oMap.Angka8 = (dr[54] is DBNull) ? 0 : Convert.ToDouble(dr[54]);
                    oMap.Angka9 = (dr[55] is DBNull) ? 0 : Convert.ToDouble(dr[55]);
                    oMap.Angka10 = Convert.ToDouble(dr[56]);
                    oMap.Angka11 = (dr[57] is DBNull) ? 0 : Convert.ToDouble(dr[57]);
                    oMap.Angka12 = (dr[58] is DBNull) ? 0 : Convert.ToDouble(dr[58]);
                    oMap.Angka13 = (dr[59] is DBNull) ? 0 : Convert.ToDouble(dr[59]);
                    oMap.Angka14 = (dr[60] is DBNull) ? 0 : Convert.ToDouble(dr[60]);
                    oMap.Angka15 = (dr[61] is DBNull) ? 0 : Convert.ToDouble(dr[61]);
                    oMap.Angka16 = (dr[62] is DBNull) ? 0 : Convert.ToDouble(dr[62]);
                    oMap.Angka17 = (dr[63] is DBNull) ? 0 : Convert.ToDouble(dr[63]);
                    oMap.Angka18 = (dr[64] is DBNull) ? 0 : Convert.ToDouble(dr[64]);
                    oMap.Angka19 = (dr[65] is DBNull) ? 0 : Convert.ToDouble(dr[65]);
                    oMap.Angka20 = (dr[66] is DBNull) ? 0 : Convert.ToDouble(dr[66]);
                    oMap.Angka21 = (dr[67] is DBNull) ? 0 : Convert.ToDouble(dr[67]);
                    oMap.Angka22 = (dr[69] is DBNull) ? 0 : Convert.ToDouble(dr[69]);
                    oMap.Angka22A = 0;
                    oMap.Angka22B = oMap.Angka22;
                    oMap.Angka23 = (dr[70] is DBNull) ? 0 : Convert.ToDouble(dr[70]);
                    oMap.Angka24 = (dr[71] is DBNull) ? 0 : Convert.ToDouble(dr[71]);
                    oMap.FlagStatus = 0;
                    oMap.Flag23 = 0;
                    oMap.Flag24 = 0;
                    oMap.MasaTahungPotong = string.Empty;
                    oMap.MasaTahungHitung = string.Empty;
                    oMap.UmrBulan = 0;
                    oMap.UmrTahun = 0;
                    oMap.FlagTunjanganPPH = 1;
                    oMap.FlagPTKP = 2;
                    oMap.FlagPegawai = 1;
                    olist.Add(oMap);

                    i = i + 1;
                }
                catch (Exception e)
                {
                    e.ToString();
                    olist = new List<ObjectMap.Data1721A1>();
                    break;
                }
            }

            return olist;
        }

        private List<ObjectMap.DS_Form_Induk> MappingDsFormInduk(List<ObjectMap.Data1721A1> dt, string kdCabang,string mainNPWP, string pathAccess)
        {
            ObjectMap.DS_Form_Induk oMap = null;
            List<ObjectMap.DS_Form_Induk> oList = new List<ObjectMap.DS_Form_Induk>();

            int i = 0;
            string datForm = string.Empty;
            int jmlPerson = 0;
            double a9 = 0;
            double gg = 0;
            double a21 = 0;

            double valC = 0;
            double valD = 0;

            string a = string.Empty, b = string.Empty, c = string.Empty, d = string.Empty,
                e = string.Empty, f = string.Empty, g = string.Empty, h = string.Empty;

            foreach (ObjectMap.Data1721A1 dr in dt)
            {
                if (dr.BranchCode == kdCabang)
                {
                    if (dr.Angka21 > 0)
                    {
                        valC = valC + dr.Angka9;
                        valD = valD + dr.Angka21;
                    }
                    else if (dr.Angka21 == 0)
                    {
                        i = i + 1;
                        a9 = a9 + dr.Angka9;
                        a21 = a21 + dr.Angka21;
                    }
                }
            }

            jmlPerson = i;

            a = TahunPajak.Trim();
            b = "0";
            c = valC.ToString();

            d = valD.ToString();
            e = a9.ToString();
            f = jmlPerson.ToString();

            gg = Convert.ToDouble(e) + Convert.ToDouble(c);
            g = gg.ToString();
            h = d;


            oMap = new ObjectMap.DS_Form_Induk();

            datForm = a + ";" + b + ";" + c + ";" + d + ";" + e + ";" + f + ";" + g + ";" + h;

            oMap.NPWP = mainNPWP.Trim();
            oMap.Kd_Form = "D113247";
            oMap.Jenis_Dokumen = "02";
            oMap.MasaPajak = Convert.ToInt32(this.MasaPajak.Trim());
            oMap.Th_Pajak = Convert.ToInt32(this.TahunPajak.Trim());
            oMap.KD_PEMBETULAN_KE = 0;
            oMap.NOBUKTI = "-";
            oMap.KD_JENIS_PAJAK = "111";
            oMap.DAT_FORM = datForm;
            oMap.TGL_REKAM = DateTime.Now;
            oMap.TGL_UBAH = DateTime.Now;
            oMap.NIP = "";

            oList.Add(oMap);

            string datForm2 = string.Empty;
            string nmPerush = string.Empty;
            string almtPerush = string.Empty;

            ObjectMap.DS_Form_Induk oMap2 = new ObjectMap.DS_Form_Induk();

            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathAccess + ";Jet OLEDB:Database Password=e_spt_21_2009;";
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = connectionString;
            con.Open();
            string strSelect = string.Empty;

            strSelect = "select NAMA_WP_PROFILE,ALAMAT from DAT_PROFILE where NPWP = '" + mainNPWP + "'";

            OleDbCommand cmdExist = con.CreateCommand();
            cmdExist.CommandText = strSelect;
            cmdExist.CommandType = CommandType.Text;

            OleDbDataReader rdExist = cmdExist.ExecuteReader();

            while (rdExist.Read())
            {
                nmPerush = rdExist[0].ToString();
                almtPerush = rdExist[1].ToString();
            }

            con.Close();
            con.Dispose();

            datForm2 = MasaPajak + ";" + TahunPajak + ";0;0;" + TahunPajak + ";" + MainNPWP + ";" + nmPerush + ";" + almtPerush + ";;;01/01/2010;" + f.ToString() + ";" + g.ToString() + ";" + h.ToString() + ";0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;" + jmlPerson + ";" + a9 + ";0;0;0;0;;0;0;0;0;0;0;0;0;;;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;;1";

            oMap2.NPWP = mainNPWP.Trim();
            oMap2.Kd_Form = "F113201".Trim();
            oMap2.Jenis_Dokumen = "01";
            oMap2.MasaPajak = Convert.ToInt32(this.MasaPajak.Trim());
            oMap2.Th_Pajak = Convert.ToInt32(this.TahunPajak.Trim());
            oMap2.KD_PEMBETULAN_KE = 0;
            oMap2.NOBUKTI = "-";
            oMap2.KD_JENIS_PAJAK = "111";
            oMap2.DAT_FORM = datForm2;
            oMap2.TGL_REKAM = DateTime.Now;
            oMap2.TGL_UBAH = DateTime.Now;
            oMap2.NIP = "";

            oList.Add(oMap2);

            return oList;
        }

        private DataTable DtDAT_PROFILE(string path)
        {
            DataTable dt = new DataTable();
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Database Password=e_spt_21_2009;";
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = connectionString;
            con.Open();

            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandText = "select * from DAT_PROFILE";
            cmd.CommandType = CommandType.Text;

            OleDbDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dt.Load(dr);
            }

            con.Close();

            return dt;
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            string filePath = Path.GetPathRoot(txtPathDBF.Text.Trim());
            string fullPath = Path.GetFullPath(txtPathDBF.Text.Trim());
            string connString = "Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=" + filePath + ";Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
            DataTable dt = new DataTable();
            IDBManager dbManager = new DBManager(DataProvider.Odbc);
            dbManager.ConnectionString = connString;
            
            try
            {
                dbManager.Open();
                dbManager.ExecuteReader(CommandType.Text, "Select * from " + fullPath);
                dt.Load(dbManager.DataReader);
            }

            catch (Exception ex)
            {
                string error = ex.ToString();
                MessageBox.Show(this, error, "Export", MessageBoxButtons.OK);
            }

            finally
            {
                dbManager.Dispose();
            }


            foreach (string item in chkListMDB.CheckedItems)
            {
                Export export = new Export();
                export.PathAccess = item;
                export.ExportREF_JABATAN_A1(dt);
                string branchCode = Path.GetFileNameWithoutExtension(item);
                string npwp = NPWP(DtDAT_PROFILE(export.PathAccess.Trim()));

                List<ObjectMap.Data1721A1> oLstData1721A1 = new List<ObjectMap.Data1721A1>();
                oLstData1721A1 = ListWithBranch(Data1721A1ToCollection(dt,item), branchCode);

                export.Export1721_A1(oLstData1721A1, MappingDsFormInduk(Data1721A1ToCollection(dt,item), branchCode,npwp,item), branchCode, npwp);
            }  
        }

        private void chAll_CheckedChanged(object sender, EventArgs e)
        {
            if (chAll.Checked == true)
            {
                for (int i = 0; i <= (chkListMDB.Items.Count - 1); i++)
                {
                    chkListMDB.SetItemCheckState(i, CheckState.Checked);
                }
            }
            else
            {
                for (int i = 0; i <= (chkListMDB.Items.Count - 1); i++)
                {
                    chkListMDB.SetItemCheckState(i, CheckState.Unchecked);
                }
            }
        }
        
    }
}